﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Odbc;
using System.Collections;
using Filmes = LI4SilverWCF.bus.Filmes;

namespace LI4SilverWCF.data
{

    class DBFilmes
    {
        OdbcConnection con;
        public DBFilmes(OdbcConnection c)
        {
            con = c;
        }

        public bool verificaUnicidadeFilme(String nome, String ano)
        {
            string query = "SELECT COD_FILME FROM FILME WHERE NOME_FILME = '" + nome + "' AND YEAR(DATA_ESTREIA) = " + int.Parse(ano);
            OdbcCommand command = new OdbcCommand(query, con);
            con.Open();
            OdbcDataReader reader = command.ExecuteReader();
            bool unico = false;

            try
            {
                if (!reader.HasRows) unico = true;
            }
            catch (Exception e)
            {
            }
            con.Close();
            return unico;
        }

        public void guardarNomeAnoFilme(String nome, String ano)
        {
            string query = "INSERT INTO FILME (NOME_FILME,CLASS_TOT,CLASSIFICACOES,VISITAS,DATA_ESTREIA) VALUES ('" + nome + "', 0, 1, 0, '" + ano + "')";
            OdbcCommand command = new OdbcCommand(query, con);
            con.Open();
            try
            {
                command.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Console.WriteLine("ERRO AO INSERIR DADOS FILME: " + e);
            }
            con.Close();
        }

        public void inserirDadosFilme(String nome, String ano, String nomeRealizador, String nomeActor, String genero, String trailer, DateTime data, String descricao, String duracao, String imagem)
        {
            string dataestreia = "CONVERT(DATETIME,'" + data.ToString("dd-MM-yyyy") + "',103)";
            string query;
            if (nomeRealizador.Length > 0)
            {
                string cod_realiz = "";
                if (nomeActor.Length > 0)
                {
                    string cod_actor = "";
                    if (genero.Length > 0)
                    {
                        string cod_gen = "";
                        if (trailer.Length > 0)
                        {
                            query = "SELECT COD_REALIZ FROM REALIZADOR WHERE NOME_REALIZ = '" + nomeRealizador + "'";
                            OdbcCommand command = new OdbcCommand(query, con);
                            con.Open();
                            OdbcDataReader reader = command.ExecuteReader();
                            if (reader.HasRows)
                            {
                                try
                                {
                                    while (reader.Read())
                                        cod_realiz = reader.GetString(0);
                                }
                                catch (Exception e)
                                {
                                }
                            }
                            else
                            {
                                con.Close();
                                con.Open();
                                query = "INSERT INTO REALIZADOR VALUES ('" + nomeRealizador + "')";
                                command = new OdbcCommand(query, con);
                                try
                                {
                                    command.ExecuteNonQuery();
                                }
                                catch (Exception e)
                                {
                                    Console.WriteLine("ERRO AO INSERIR DADOS FILME - 0: " + e);
                                }
                                con.Close();
                                query = "SELECT COD_REALIZ FROM REALIZADOR WHERE NOME_REALIZ = '" + nomeRealizador + "'";
                                command = new OdbcCommand(query, con);
                                con.Open();
                                reader = command.ExecuteReader();
                                try
                                {
                                    while (reader.Read())
                                        cod_realiz = reader.GetString(0);
                                }
                                catch (Exception e)
                                {
                                }
                            }
                            con.Close();

                            query = "SELECT COD_ACTOR FROM ACTOR WHERE NOME_ACTOR = '" + nomeActor + "'";
                            command = new OdbcCommand(query, con);
                            con.Open();
                            reader = command.ExecuteReader();
                            if (reader.HasRows)
                            {
                                try
                                {
                                    while (reader.Read())
                                        cod_actor = reader.GetString(0);
                                }
                                catch (Exception e)
                                {
                                }
                            }
                            else
                            {
                                con.Close();
                                con.Open();
                                query = "INSERT INTO ACTOR VALUES ('" + nomeActor + "')";
                                command = new OdbcCommand(query, con);
                                try
                                {
                                    command.ExecuteNonQuery();
                                }
                                catch (Exception e)
                                {
                                    Console.WriteLine("ERRO AO INSERIR DADOS FILME - 1: " + e);
                                }
                                con.Close();
                                query = "SELECT COD_ACTOR FROM ACTOR WHERE NOME_ACTOR = '" + nomeActor + "'";
                                command = new OdbcCommand(query, con);
                                con.Open();
                                reader = command.ExecuteReader();
                                try
                                {
                                    while (reader.Read())
                                        cod_actor = reader.GetString(0);
                                }
                                catch (Exception e)
                                {
                                }
                            }
                            con.Close();

                            query = "SELECT COD_GEN FROM GENERO WHERE NOME_GEN = '" + genero + "'";
                            command = new OdbcCommand(query, con);
                            con.Open();
                            reader = command.ExecuteReader();
                            if (reader.HasRows)
                            {
                                try
                                {
                                    while (reader.Read())
                                        cod_gen = reader.GetString(0);
                                }
                                catch (Exception e)
                                {
                                }
                            }
                            else
                            {
                                con.Close();
                                con.Open();
                                query = "INSERT INTO GENERO VALUES ('" + genero + "')";
                                command = new OdbcCommand(query, con);
                                try
                                {
                                    command.ExecuteNonQuery();
                                }
                                catch (Exception e)
                                {
                                    Console.WriteLine("ERRO AO INSERIR DADOS FILME - 2: " + e);
                                }
                                con.Close();
                                query = "SELECT COD_GEN FROM GENERO WHERE NOME_GEN = '" + genero + "'";
                                command = new OdbcCommand(query, con);
                                con.Open();
                                reader = command.ExecuteReader();
                                try
                                {
                                    while (reader.Read())
                                        cod_gen = reader.GetString(0);
                                }
                                catch (Exception e)
                                {
                                }
                            }
                            con.Close();

                            query = "UPDATE FILME SET COD_REALIZ = " + int.Parse(cod_realiz) + ", COD_ACTOR = " + int.Parse(cod_actor) + ", COD_GEN = " + int.Parse(cod_gen) + ", TRAILER = '" + trailer + "', DATA_ESTREIA = " + dataestreia + ", DESCRICAO = '" + descricao + "', DURACAO = '" + duracao + "', URL_IMAGEM = '" + imagem + "' WHERE NOME_FILME = '" + nome + "' AND YEAR(DATA_ESTREIA) = " + int.Parse(ano);
                            command = new OdbcCommand(query, con);
                            con.Open();
                            try
                            {
                                command.ExecuteNonQuery();
                            }
                            catch (Exception e)
                            {
                                Console.WriteLine("ERRO AO INSERIR DADOS FILME - 3: " + e);
                            }
                            con.Close();
                        }
                        else
                        {
                            query = "SELECT COD_REALIZ FROM REALIZADOR WHERE NOME_REALIZ = '" + nomeRealizador + "'";
                            OdbcCommand command = new OdbcCommand(query, con);
                            con.Open();
                            OdbcDataReader reader = command.ExecuteReader();
                            if (reader.HasRows)
                            {
                                try
                                {
                                    while (reader.Read())
                                        cod_realiz = reader.GetString(0);
                                }
                                catch (Exception e)
                                {
                                }
                            }
                            else
                            {
                                con.Close();
                                con.Open();
                                query = "INSERT INTO REALIZADOR VALUES ('" + nomeRealizador + "')";
                                command = new OdbcCommand(query, con);
                                try
                                {
                                    command.ExecuteNonQuery();
                                }
                                catch (Exception e)
                                {
                                    Console.WriteLine("ERRO AO INSERIR DADOS FILME - 4: " + e);
                                }
                                con.Close();
                                query = "SELECT COD_REALIZ FROM REALIZADOR WHERE NOME_REALIZ = '" + nomeRealizador + "'";
                                command = new OdbcCommand(query, con);
                                con.Open();
                                reader = command.ExecuteReader();
                                try
                                {
                                    while (reader.Read())
                                        cod_realiz = reader.GetString(0);
                                }
                                catch (Exception e)
                                {
                                }
                            }
                            con.Close();

                            query = "SELECT COD_ACTOR FROM ACTOR WHERE NOME_ACTOR = '" + nomeActor + "'";
                            command = new OdbcCommand(query, con);
                            con.Open();
                            reader = command.ExecuteReader();
                            if (reader.HasRows)
                            {
                                try
                                {
                                    while (reader.Read())
                                        cod_actor = reader.GetString(0);
                                }
                                catch (Exception e)
                                {
                                }
                            }
                            else
                            {
                                con.Close();
                                con.Open();
                                query = "INSERT INTO ACTOR VALUES ('" + nomeActor + "')";
                                command = new OdbcCommand(query, con);
                                try
                                {
                                    command.ExecuteNonQuery();
                                }
                                catch (Exception e)
                                {
                                    Console.WriteLine("ERRO AO INSERIR DADOS FILME - 5: " + e);
                                }
                                con.Close();
                                query = "SELECT COD_ACTOR FROM ACTOR WHERE NOME_ACTOR = '" + nomeActor + "'";
                                command = new OdbcCommand(query, con);
                                con.Open();
                                reader = command.ExecuteReader();
                                try
                                {
                                    while (reader.Read())
                                        cod_actor = reader.GetString(0);
                                }
                                catch (Exception e)
                                {
                                }
                            }
                            con.Close();

                            query = "SELECT COD_GEN FROM GENERO WHERE NOME_GEN = '" + genero + "'";
                            command = new OdbcCommand(query, con);
                            con.Open();
                            reader = command.ExecuteReader();
                            if (reader.HasRows)
                            {
                                try
                                {
                                    while (reader.Read())
                                        cod_gen = reader.GetString(0);
                                }
                                catch (Exception e)
                                {
                                }
                            }
                            else
                            {
                                con.Close();
                                con.Open();
                                query = "INSERT INTO GENERO VALUES ('" + genero + "')";
                                command = new OdbcCommand(query, con);
                                try
                                {
                                    command.ExecuteNonQuery();
                                }
                                catch (Exception e)
                                {
                                    Console.WriteLine("ERRO AO INSERIR DADOS FILME - 6: " + e);
                                }
                                con.Close();
                                query = "SELECT COD_GEN FROM GENERO WHERE NOME_GEN = '" + genero + "'";
                                command = new OdbcCommand(query, con);
                                con.Open();
                                reader = command.ExecuteReader();
                                try
                                {
                                    while (reader.Read())
                                        cod_gen = reader.GetString(0);
                                }
                                catch (Exception e)
                                {
                                }
                            }
                            con.Close();

                            query = "UPDATE FILME SET COD_REALIZ = " + int.Parse(cod_realiz) + ", COD_ACTOR = " + int.Parse(cod_actor) + ", COD_GEN = " + int.Parse(cod_gen) + ", DATA_ESTREIA = " + dataestreia + ", DESCRICAO = '" + descricao + "', DURACAO = '" + duracao + "', URL_IMAGEM = '" + imagem + "' WHERE NOME_FILME = '" + nome + "' AND YEAR(DATA_ESTREIA) = " + int.Parse(ano);
                            command = new OdbcCommand(query, con);
                            con.Open();
                            try
                            {
                                command.ExecuteNonQuery();
                            }
                            catch (Exception e)
                            {
                                Console.WriteLine("ERRO AO INSERIR DADOS FILME - 7: " + e);
                            }
                            con.Close();
                        }
                    }
                    else
                    {
                        if (trailer.Length > 0)
                        {
                            query = "SELECT COD_REALIZ FROM REALIZADOR WHERE NOME_REALIZ = '" + nomeRealizador + "'";
                            OdbcCommand command = new OdbcCommand(query, con);
                            con.Open();
                            OdbcDataReader reader = command.ExecuteReader();
                            if (reader.HasRows)
                            {
                                try
                                {
                                    while (reader.Read())
                                        cod_realiz = reader.GetString(0);
                                }
                                catch (Exception e)
                                {
                                }
                            }
                            else
                            {
                                con.Close();
                                con.Open();
                                query = "INSERT INTO REALIZADOR VALUES ('" + nomeRealizador + "')";
                                command = new OdbcCommand(query, con);
                                try
                                {
                                    command.ExecuteNonQuery();
                                }
                                catch (Exception e)
                                {
                                    Console.WriteLine("ERRO AO INSERIR DADOS FILME: " + e);
                                }
                                con.Close();
                                query = "SELECT COD_REALIZ FROM REALIZADOR WHERE NOME_REALIZ = '" + nomeRealizador + "'";
                                command = new OdbcCommand(query, con);
                                con.Open();
                                reader = command.ExecuteReader();
                                try
                                {
                                    while (reader.Read())
                                        cod_realiz = reader.GetString(0);
                                }
                                catch (Exception e)
                                {
                                }
                            }
                            con.Close();

                            query = "SELECT COD_ACTOR FROM ACTOR WHERE NOME_ACTOR = '" + nomeActor + "'";
                            command = new OdbcCommand(query, con);
                            con.Open();
                            reader = command.ExecuteReader();
                            if (reader.HasRows)
                            {
                                try
                                {
                                    while (reader.Read())
                                        cod_actor = reader.GetString(0);
                                }
                                catch (Exception e)
                                {
                                }
                            }
                            else
                            {
                                con.Close();
                                con.Open();
                                query = "INSERT INTO ACTOR VALUES ('" + nomeActor + "')";
                                command = new OdbcCommand(query, con);
                                try
                                {
                                    command.ExecuteNonQuery();
                                }
                                catch (Exception e)
                                {
                                    Console.WriteLine("ERRO AO INSERIR DADOS FILME: " + e);
                                }
                                con.Close();
                                query = "SELECT COD_ACTOR FROM ACTOR WHERE NOME_ACTOR = '" + nomeActor + "'";
                                command = new OdbcCommand(query, con);
                                con.Open();
                                reader = command.ExecuteReader();
                                try
                                {
                                    while (reader.Read())
                                        cod_actor = reader.GetString(0);
                                }
                                catch (Exception e)
                                {
                                }
                            }
                            con.Close();

                            query = "UPDATE FILME SET COD_REALIZ = " + int.Parse(cod_realiz) + ", COD_ACTOR = " + int.Parse(cod_actor) + ", TRAILER = '" + trailer + "', DATA_ESTREIA = " + dataestreia + ", DESCRICAO = '" + descricao + "', DURACAO = '" + duracao + "', URL_IMAGEM = '" + imagem + "' WHERE NOME_FILME = '" + nome + "' AND YEAR(DATA_ESTREIA) = " + int.Parse(ano);
                            command = new OdbcCommand(query, con);
                            con.Open();
                            try
                            {
                                command.ExecuteNonQuery();
                            }
                            catch (Exception e)
                            {
                                Console.WriteLine("ERRO AO INSERIR DADOS FILME: " + e);
                            }
                            con.Close();
                        }
                        else
                        {
                            query = "SELECT COD_REALIZ FROM REALIZADOR WHERE NOME_REALIZ = '" + nomeRealizador + "'";
                            OdbcCommand command = new OdbcCommand(query, con);
                            con.Open();
                            OdbcDataReader reader = command.ExecuteReader();
                            if (reader.HasRows)
                            {
                                try
                                {
                                    while (reader.Read())
                                        cod_realiz = reader.GetString(0);
                                }
                                catch (Exception e)
                                {
                                }
                            }
                            else
                            {
                                con.Close();
                                con.Open();
                                query = "INSERT INTO REALIZADOR VALUES ('" + nomeRealizador + "')";
                                command = new OdbcCommand(query, con);
                                try
                                {
                                    command.ExecuteNonQuery();
                                }
                                catch (Exception e)
                                {
                                    Console.WriteLine("ERRO AO INSERIR DADOS FILME - 11: " + e);
                                }
                                con.Close();
                                query = "SELECT COD_REALIZ FROM REALIZADOR WHERE NOME_REALIZ = '" + nomeRealizador + "'";
                                command = new OdbcCommand(query, con);
                                con.Open();
                                reader = command.ExecuteReader();
                                try
                                {
                                    while (reader.Read())
                                        cod_realiz = reader.GetString(0);
                                }
                                catch (Exception e)
                                {
                                }
                            }
                            con.Close();

                            query = "SELECT COD_ACTOR FROM ACTOR WHERE NOME_ACTOR = '" + nomeActor + "'";
                            command = new OdbcCommand(query, con);
                            con.Open();
                            reader = command.ExecuteReader();
                            if (reader.HasRows)
                            {
                                try
                                {
                                    while (reader.Read())
                                        cod_actor = reader.GetString(0);
                                }
                                catch (Exception e)
                                {
                                }
                            }
                            else
                            {
                                con.Close();
                                con.Open();
                                query = "INSERT INTO ACTOR VALUES ('" + nomeActor + "')";
                                command = new OdbcCommand(query, con);
                                try
                                {
                                    command.ExecuteNonQuery();
                                }
                                catch (Exception e)
                                {
                                    Console.WriteLine("ERRO AO INSERIR DADOS FILME - 12: " + e);
                                }
                                con.Close();
                                query = "SELECT COD_ACTOR FROM ACTOR WHERE NOME_ACTOR = '" + nomeActor + "'";
                                command = new OdbcCommand(query, con);
                                con.Open();
                                reader = command.ExecuteReader();
                                try
                                {
                                    while (reader.Read())
                                        cod_actor = reader.GetString(0);
                                }
                                catch (Exception e)
                                {
                                }
                            }
                            con.Close();

                            query = "UPDATE FILME SET COD_REALIZ = " + int.Parse(cod_realiz) + ", COD_ACTOR = " + int.Parse(cod_actor) + ", DATA_ESTREIA = " + dataestreia + ", DESCRICAO = '" + descricao + "', DURACAO = '" + duracao + "', URL_IMAGEM = '" + imagem + "' WHERE NOME_FILME = '" + nome + "' AND YEAR(DATA_ESTREIA) = " + int.Parse(ano);
                            command = new OdbcCommand(query, con);
                            con.Open();
                            try
                            {
                                command.ExecuteNonQuery();
                            }
                            catch (Exception e)
                            {
                                Console.WriteLine("ERRO AO INSERIR DADOS FILME - 13: " + e);
                            }
                            con.Close();
                        }
                    }
                }
                else
                {
                    if (genero.Length > 0)
                    {
                        string cod_gen = "";
                        if (trailer.Length > 0)
                        {
                            query = "SELECT COD_REALIZ FROM REALIZADOR WHERE NOME_REALIZ = '" + nomeRealizador + "'";
                            OdbcCommand command = new OdbcCommand(query, con);
                            con.Open();
                            OdbcDataReader reader = command.ExecuteReader();
                            if (reader.HasRows)
                            {
                                try
                                {
                                    while (reader.Read())
                                        cod_realiz = reader.GetString(0);
                                }
                                catch (Exception e)
                                {
                                }
                            }
                            else
                            {
                                con.Close();
                                con.Open();
                                query = "INSERT INTO REALIZADOR VALUES ('" + nomeRealizador + "')";
                                command = new OdbcCommand(query, con);
                                try
                                {
                                    command.ExecuteNonQuery();
                                }
                                catch (Exception e)
                                {
                                    Console.WriteLine("ERRO AO INSERIR DADOS FILME - 134: " + e);
                                }
                                con.Close();
                                query = "SELECT COD_REALIZ FROM REALIZADOR WHERE NOME_REALIZ = '" + nomeRealizador + "'";
                                command = new OdbcCommand(query, con);
                                con.Open();
                                reader = command.ExecuteReader();
                                try
                                {
                                    while (reader.Read())
                                        cod_realiz = reader.GetString(0);
                                }
                                catch (Exception e)
                                {
                                }
                            }
                            con.Close();

                            query = "SELECT COD_GEN FROM GENERO WHERE NOME_GEN = '" + genero + "'";
                            command = new OdbcCommand(query, con);
                            con.Open();
                            reader = command.ExecuteReader();
                            if (reader.HasRows)
                            {
                                try
                                {
                                    while (reader.Read())
                                        cod_gen = reader.GetString(0);
                                }
                                catch (Exception e)
                                {
                                }
                            }
                            else
                            {
                                con.Close();
                                con.Open();
                                query = "INSERT INTO GENERO VALUES ('" + genero + "')";
                                command = new OdbcCommand(query, con);
                                try
                                {
                                    command.ExecuteNonQuery();
                                }
                                catch (Exception e)
                                {
                                    Console.WriteLine("ERRO AO INSERIR DADOS FILME - 14: " + e);
                                }
                                con.Close();
                                query = "SELECT COD_GEN FROM GENERO WHERE NOME_GEN = '" + genero + "'";
                                command = new OdbcCommand(query, con);
                                con.Open();
                                reader = command.ExecuteReader();
                                try
                                {
                                    while (reader.Read())
                                        cod_gen = reader.GetString(0);
                                }
                                catch (Exception e)
                                {
                                }
                            }
                            con.Close();

                            query = "UPDATE FILME SET COD_REALIZ = " + int.Parse(cod_realiz) + ", COD_GEN = " + int.Parse(cod_gen) + ", TRAILER = '" + trailer + "', DATA_ESTREIA = " + dataestreia + ", DESCRICAO = '" + descricao + "', DURACAO = '" + duracao + "', URL_IMAGEM = '" + imagem + "' WHERE NOME_FILME = '" + nome + "' AND YEAR(DATA_ESTREIA) = " + int.Parse(ano);
                            command = new OdbcCommand(query, con);
                            con.Open();
                            try
                            {
                                command.ExecuteNonQuery();
                            }
                            catch (Exception e)
                            {
                                Console.WriteLine("ERRO AO INSERIR DADOS FILME - 143: " + e);
                            }
                            con.Close();
                        }
                        else
                        {
                            query = "SELECT COD_REALIZ FROM REALIZADOR WHERE NOME_REALIZ = '" + nomeRealizador + "'";
                            OdbcCommand command = new OdbcCommand(query, con);
                            con.Open();
                            OdbcDataReader reader = command.ExecuteReader();
                            if (reader.HasRows)
                            {
                                try
                                {
                                    while (reader.Read())
                                        cod_realiz = reader.GetString(0);
                                }
                                catch (Exception e)
                                {
                                }
                            }
                            else
                            {
                                con.Close();
                                con.Open();
                                query = "INSERT INTO REALIZADOR VALUES ('" + nomeRealizador + "')";
                                command = new OdbcCommand(query, con);
                                try
                                {
                                    command.ExecuteNonQuery();
                                }
                                catch (Exception e)
                                {
                                    Console.WriteLine("ERRO AO INSERIR DADOS FILME - 15: " + e);
                                }
                                con.Close();
                                query = "SELECT COD_REALIZ FROM REALIZADOR WHERE NOME_REALIZ = '" + nomeRealizador + "'";
                                command = new OdbcCommand(query, con);
                                con.Open();
                                reader = command.ExecuteReader();
                                try
                                {
                                    while (reader.Read())
                                        cod_realiz = reader.GetString(0);
                                }
                                catch (Exception e)
                                {
                                }
                            }
                            con.Close();

                            query = "SELECT COD_GEN FROM GENERO WHERE NOME_GEN = '" + genero + "'";
                            command = new OdbcCommand(query, con);
                            con.Open();
                            reader = command.ExecuteReader();
                            if (reader.HasRows)
                            {
                                try
                                {
                                    while (reader.Read())
                                        cod_gen = reader.GetString(0);
                                }
                                catch (Exception e)
                                {
                                }
                            }
                            else
                            {
                                con.Close();
                                con.Open();
                                query = "INSERT INTO GENERO VALUES ('" + genero + "')";
                                command = new OdbcCommand(query, con);
                                try
                                {
                                    command.ExecuteNonQuery();
                                }
                                catch (Exception e)
                                {
                                    Console.WriteLine("ERRO AO INSERIR DADOS FILME - 1143: " + e);
                                }
                                con.Close();
                                query = "SELECT COD_GEN FROM GENERO WHERE NOME_GEN = '" + genero + "'";
                                command = new OdbcCommand(query, con);
                                con.Open();
                                reader = command.ExecuteReader();
                                try
                                {
                                    while (reader.Read())
                                        cod_gen = reader.GetString(0);
                                }
                                catch (Exception e)
                                {
                                }
                            }
                            con.Close();

                            query = "UPDATE FILME SET COD_REALIZ = " + int.Parse(cod_realiz) + ", COD_GEN = " + int.Parse(cod_gen) + ", DATA_ESTREIA = " + dataestreia + ", DESCRICAO = '" + descricao + "', DURACAO = '" + duracao + "', URL_IMAGEM = '" + imagem + "' WHERE NOME_FILME = '" + nome + "' AND YEAR(DATA_ESTREIA) = " + int.Parse(ano);
                            command = new OdbcCommand(query, con);
                            con.Open();
                            try
                            {
                                command.ExecuteNonQuery();
                            }
                            catch (Exception e)
                            {
                                Console.WriteLine("ERRO AO INSERIR DADOS FILME - 1413: " + e);
                            }
                            con.Close();
                        }
                    }
                    else
                    {
                        if (trailer.Length > 0)
                        {
                            query = "SELECT COD_REALIZ FROM REALIZADOR WHERE NOME_REALIZ = '" + nomeRealizador + "'";
                            OdbcCommand command = new OdbcCommand(query, con);
                            con.Open();
                            OdbcDataReader reader = command.ExecuteReader();
                            if (reader.HasRows)
                            {
                                try
                                {
                                    while (reader.Read())
                                        cod_realiz = reader.GetString(0);
                                }
                                catch (Exception e)
                                {
                                }
                            }
                            else
                            {
                                con.Close();
                                con.Open();
                                query = "INSERT INTO REALIZADOR VALUES ('" + nomeRealizador + "')";
                                command = new OdbcCommand(query, con);
                                try
                                {
                                    command.ExecuteNonQuery();
                                }
                                catch (Exception e)
                                {
                                    Console.WriteLine("ERRO AO INSERIR DADOS FILME - 165: " + e);
                                }
                                con.Close();
                                query = "SELECT COD_REALIZ FROM REALIZADOR WHERE NOME_REALIZ = '" + nomeRealizador + "'";
                                command = new OdbcCommand(query, con);
                                con.Open();
                                reader = command.ExecuteReader();
                                try
                                {
                                    while (reader.Read())
                                        cod_realiz = reader.GetString(0);
                                }
                                catch (Exception e)
                                {
                                }
                            }
                            con.Close();

                            query = "UPDATE FILME SET COD_REALIZ = " + int.Parse(cod_realiz) + ", TRAILER = '" + trailer + "', DATA_ESTREIA = " + dataestreia + ", DESCRICAO = '" + descricao + "', DURACAO = '" + duracao + "', URL_IMAGEM = '" + imagem + "' WHERE NOME_FILME = '" + nome + "' AND YEAR(DATA_ESTREIA) = " + int.Parse(ano);
                            command = new OdbcCommand(query, con);
                            con.Open();
                            try
                            {
                                command.ExecuteNonQuery();
                            }
                            catch (Exception e)
                            {
                                Console.WriteLine("ERRO AO INSERIR DADOS FILME - 174: " + e);
                            }
                            con.Close();
                        }
                        else
                        {
                            query = "SELECT COD_REALIZ FROM REALIZADOR WHERE NOME_REALIZ = '" + nomeRealizador + "'";
                            OdbcCommand command = new OdbcCommand(query, con);
                            con.Open();
                            OdbcDataReader reader = command.ExecuteReader();
                            if (reader.HasRows)
                            {
                                try
                                {
                                    while (reader.Read())
                                        cod_realiz = reader.GetString(0);
                                }
                                catch (Exception e)
                                {
                                }
                            }
                            else
                            {
                                con.Close();
                                con.Open();
                                query = "INSERT INTO REALIZADOR VALUES ('" + nomeRealizador + "')";
                                command = new OdbcCommand(query, con);
                                try
                                {
                                    command.ExecuteNonQuery();
                                }
                                catch (Exception e)
                                {
                                    Console.WriteLine("ERRO AO INSERIR DADOS FILME - 246: " + e);
                                }
                                con.Close();
                                query = "SELECT COD_REALIZ FROM REALIZADOR WHERE NOME_REALIZ = '" + nomeRealizador + "'";
                                command = new OdbcCommand(query, con);
                                con.Open();
                                reader = command.ExecuteReader();
                                try
                                {
                                    while (reader.Read())
                                        cod_realiz = reader.GetString(0);
                                }
                                catch (Exception e)
                                {
                                }
                            }
                            con.Close();

                            query = "UPDATE FILME SET COD_REALIZ = " + int.Parse(cod_realiz) + ", DATA_ESTREIA = " + dataestreia + ", DESCRICAO = '" + descricao + "', DURACAO = '" + duracao + "', URL_IMAGEM = '" + imagem + "' WHERE NOME_FILME = '" + nome + "' AND YEAR(DATA_ESTREIA) = " + int.Parse(ano);
                            command = new OdbcCommand(query, con);
                            con.Open();
                            try
                            {
                                command.ExecuteNonQuery();
                            }
                            catch (Exception e)
                            {
                                Console.WriteLine("ERRO AO INSERIR DADOS FILME - 2254: " + e);
                            }
                            con.Close();
                        }
                    }
                }
            }
            else
            {
                if (nomeActor.Length > 0)
                {
                    string cod_actor = "";
                    if (genero.Length > 0)
                    {
                        string cod_gen = "";
                        if (trailer.Length > 0)
                        {
                            query = "SELECT COD_ACTOR FROM ACTOR WHERE NOME_ACTOR = '" + nomeActor + "'";
                            OdbcCommand command = new OdbcCommand(query, con);
                            con.Open();
                            OdbcDataReader reader = command.ExecuteReader();
                            if (reader.HasRows)
                            {
                                try
                                {
                                    while (reader.Read())
                                        cod_actor = reader.GetString(0);
                                }
                                catch (Exception e)
                                {
                                }
                            }
                            else
                            {
                                con.Close();
                                con.Open();
                                query = "INSERT INTO ACTOR VALUES ('" + nomeActor + "')";
                                command = new OdbcCommand(query, con);
                                try
                                {
                                    command.ExecuteNonQuery();
                                }
                                catch (Exception e)
                                {
                                    Console.WriteLine("ERRO AO INSERIR DADOS FILME: " + e);
                                }
                                con.Close();
                                query = "SELECT COD_ACTOR FROM ACTOR WHERE NOME_ACTOR = '" + nomeActor + "'";
                                command = new OdbcCommand(query, con);
                                con.Open();
                                reader = command.ExecuteReader();
                                try
                                {
                                    while (reader.Read())
                                        cod_actor = reader.GetString(0);
                                }
                                catch (Exception e)
                                {
                                }
                            }
                            con.Close();

                            query = "SELECT COD_GEN FROM GENERO WHERE NOME_GEN = '" + genero + "'";
                            command = new OdbcCommand(query, con);
                            con.Open();
                            reader = command.ExecuteReader();
                            if (reader.HasRows)
                            {
                                try
                                {
                                    while (reader.Read())
                                        cod_gen = reader.GetString(0);
                                }
                                catch (Exception e)
                                {
                                }
                            }
                            else
                            {
                                con.Close();
                                con.Open();
                                query = "INSERT INTO GENERO VALUES ('" + genero + "')";
                                command = new OdbcCommand(query, con);
                                try
                                {
                                    command.ExecuteNonQuery();
                                }
                                catch (Exception e)
                                {
                                    Console.WriteLine("ERRO AO INSERIR DADOS FILME: " + e);
                                }
                                con.Close();
                                query = "SELECT COD_GEN FROM GENERO WHERE NOME_GEN = '" + genero + "'";
                                command = new OdbcCommand(query, con);
                                con.Open();
                                reader = command.ExecuteReader();
                                try
                                {
                                    while (reader.Read())
                                        cod_gen = reader.GetString(0);
                                }
                                catch (Exception e)
                                {
                                }
                            }
                            con.Close();

                            query = "UPDATE FILME SET COD_ACTOR = " + int.Parse(cod_actor) + ", COD_GEN = " + int.Parse(cod_gen) + ", TRAILER = '" + trailer + "', DATA_ESTREIA = " + dataestreia + ", DESCRICAO = '" + descricao + "', DURACAO = '" + duracao + "', URL_IMAGEM = '" + imagem + "' WHERE NOME_FILME = '" + nome + "' AND YEAR(DATA_ESTREIA) = " + int.Parse(ano);
                            command = new OdbcCommand(query, con);
                            con.Open();
                            try
                            {
                                command.ExecuteNonQuery();
                            }
                            catch (Exception e)
                            {
                                Console.WriteLine("ERRO AO INSERIR DADOS FILME: " + e);
                            }
                            con.Close();
                        }
                        else
                        {
                            query = "SELECT COD_ACTOR FROM ACTOR WHERE NOME_ACTOR = '" + nomeActor + "'";
                            OdbcCommand command = new OdbcCommand(query, con);
                            con.Open();
                            OdbcDataReader reader = command.ExecuteReader();
                            if (reader.HasRows)
                            {
                                try
                                {
                                    while (reader.Read())
                                        cod_actor = reader.GetString(0);
                                }
                                catch (Exception e)
                                {
                                }
                            }
                            else
                            {
                                con.Close();
                                con.Open();
                                query = "INSERT INTO ACTOR VALUES ('" + nomeActor + "')";
                                command = new OdbcCommand(query, con);
                                try
                                {
                                    command.ExecuteNonQuery();
                                }
                                catch (Exception e)
                                {
                                    Console.WriteLine("ERRO AO INSERIR DADOS FILME: " + e);
                                }
                                con.Close();
                                query = "SELECT COD_ACTOR FROM ACTOR WHERE NOME_ACTOR = '" + nomeActor + "'";
                                command = new OdbcCommand(query, con);
                                con.Open();
                                reader = command.ExecuteReader();
                                try
                                {
                                    while (reader.Read())
                                        cod_actor = reader.GetString(0);
                                }
                                catch (Exception e)
                                {
                                }
                            }
                            con.Close();

                            query = "SELECT COD_GEN FROM GENERO WHERE NOME_GEN = '" + genero + "'";
                            command = new OdbcCommand(query, con);
                            con.Open();
                            reader = command.ExecuteReader();
                            if (reader.HasRows)
                            {
                                try
                                {
                                    while (reader.Read())
                                        cod_gen = reader.GetString(0);
                                }
                                catch (Exception e)
                                {
                                }
                            }
                            else
                            {
                                con.Close();
                                con.Open();
                                query = "INSERT INTO GENERO VALUES ('" + genero + "')";
                                command = new OdbcCommand(query, con);
                                try
                                {
                                    command.ExecuteNonQuery();
                                }
                                catch (Exception e)
                                {
                                    Console.WriteLine("ERRO AO INSERIR DADOS FILME: " + e);
                                }
                                con.Close();
                                query = "SELECT COD_GEN FROM GENERO WHERE NOME_GEN = '" + genero + "'";
                                command = new OdbcCommand(query, con);
                                con.Open();
                                reader = command.ExecuteReader();
                                try
                                {
                                    while (reader.Read())
                                        cod_gen = reader.GetString(0);
                                }
                                catch (Exception e)
                                {
                                }
                            }
                            con.Close();

                            query = "UPDATE FILME SET COD_ACTOR = " + int.Parse(cod_actor) + ", COD_GEN = " + int.Parse(cod_gen) + ", DATA_ESTREIA = " + dataestreia + ", DESCRICAO = '" + descricao + "', DURACAO = '" + duracao + "', URL_IMAGEM = '" + imagem + "' WHERE NOME_FILME = '" + nome + "' AND YEAR(DATA_ESTREIA) = " + int.Parse(ano);
                            command = new OdbcCommand(query, con);
                            con.Open();
                            try
                            {
                                command.ExecuteNonQuery();
                            }
                            catch (Exception e)
                            {
                                Console.WriteLine("ERRO AO INSERIR DADOS FILME: " + e);
                            }
                            con.Close();
                        }
                    }
                    else
                    {
                        if (trailer.Length > 0)
                        {
                            query = "SELECT COD_ACTOR FROM ACTOR WHERE NOME_ACTOR = '" + nomeActor + "'";
                            OdbcCommand command = new OdbcCommand(query, con);
                            con.Open();
                            OdbcDataReader reader = command.ExecuteReader();
                            if (reader.HasRows)
                            {
                                try
                                {
                                    while (reader.Read())
                                        cod_actor = reader.GetString(0);
                                }
                                catch (Exception e)
                                {
                                }
                            }
                            else
                            {
                                con.Close();
                                con.Open();
                                query = "INSERT INTO ACTOR VALUES ('" + nomeActor + "')";
                                command = new OdbcCommand(query, con);
                                try
                                {
                                    command.ExecuteNonQuery();
                                }
                                catch (Exception e)
                                {
                                    Console.WriteLine("ERRO AO INSERIR DADOS FILME: " + e);
                                }
                                con.Close();
                                query = "SELECT COD_ACTOR FROM ACTOR WHERE NOME_ACTOR = '" + nomeActor + "'";
                                command = new OdbcCommand(query, con);
                                con.Open();
                                reader = command.ExecuteReader();
                                try
                                {
                                    while (reader.Read())
                                        cod_actor = reader.GetString(0);
                                }
                                catch (Exception e)
                                {
                                }
                            }
                            con.Close();

                            query = "UPDATE FILME SET COD_ACTOR = " + int.Parse(cod_actor) + ", TRAILER = '" + trailer + "', DATA_ESTREIA = " + dataestreia + ", DESCRICAO = '" + descricao + "', DURACAO = '" + duracao + "', URL_IMAGEM = '" + imagem + "' WHERE NOME_FILME = '" + nome + "' AND YEAR(DATA_ESTREIA) = " + int.Parse(ano);
                            command = new OdbcCommand(query, con);
                            con.Open();
                            try
                            {
                                command.ExecuteNonQuery();
                            }
                            catch (Exception e)
                            {
                                Console.WriteLine("ERRO AO INSERIR DADOS FILME: " + e);
                            }
                            con.Close();
                        }
                        else
                        {
                            query = "SELECT COD_ACTOR FROM ACTOR WHERE NOME_ACTOR = '" + nomeActor + "'";
                            OdbcCommand command = new OdbcCommand(query, con);
                            con.Open();
                            OdbcDataReader reader = command.ExecuteReader();
                            if (reader.HasRows)
                            {
                                try
                                {
                                    while (reader.Read())
                                        cod_actor = reader.GetString(0);
                                }
                                catch (Exception e)
                                {
                                }
                            }
                            else
                            {
                                con.Close();
                                con.Open();
                                query = "INSERT INTO ACTOR VALUES ('" + nomeActor + "')";
                                command = new OdbcCommand(query, con);
                                try
                                {
                                    command.ExecuteNonQuery();
                                }
                                catch (Exception e)
                                {
                                    Console.WriteLine("ERRO AO INSERIR DADOS FILME: " + e);
                                }
                                con.Close();
                                query = "SELECT COD_ACTOR FROM ACTOR WHERE NOME_ACTOR = '" + nomeActor + "'";
                                command = new OdbcCommand(query, con);
                                con.Open();
                                reader = command.ExecuteReader();
                                try
                                {
                                    while (reader.Read())
                                        cod_actor = reader.GetString(0);
                                }
                                catch (Exception e)
                                {
                                }
                            }
                            con.Close();

                            query = "UPDATE FILME SET COD_ACTOR = " + int.Parse(cod_actor) + ", DATA_ESTREIA = " + dataestreia + ", DESCRICAO = '" + descricao + "', DURACAO = '" + duracao + "', URL_IMAGEM = '" + imagem + "' WHERE NOME_FILME = '" + nome + "' AND YEAR(DATA_ESTREIA) = " + int.Parse(ano);
                            command = new OdbcCommand(query, con);
                            con.Open();
                            try
                            {
                                command.ExecuteNonQuery();
                            }
                            catch (Exception e)
                            {
                                Console.WriteLine("ERRO AO INSERIR DADOS FILME: " + e);
                            }
                            con.Close();
                        }
                    }
                }
                else
                {
                    if (genero.Length > 0)
                    {
                        string cod_gen = "";
                        if (trailer.Length > 0)
                        {
                            query = "SELECT COD_GEN FROM GENERO WHERE NOME_GEN = '" + genero + "'";
                            OdbcCommand command = new OdbcCommand(query, con);
                            con.Open();
                            OdbcDataReader reader = command.ExecuteReader();
                            if (reader.HasRows)
                            {
                                try
                                {
                                    while (reader.Read())
                                        cod_gen = reader.GetString(0);
                                }
                                catch (Exception e)
                                {
                                }
                            }
                            else
                            {
                                con.Close();
                                con.Open();
                                query = "INSERT INTO GENERO VALUES ('" + genero + "')";
                                command = new OdbcCommand(query, con);
                                try
                                {
                                    command.ExecuteNonQuery();
                                }
                                catch (Exception e)
                                {
                                    Console.WriteLine("ERRO AO INSERIR DADOS FILME: " + e);
                                }
                                con.Close();
                                query = "SELECT COD_GEN FROM GENERO WHERE NOME_GEN = '" + genero + "'";
                                command = new OdbcCommand(query, con);
                                con.Open();
                                reader = command.ExecuteReader();
                                try
                                {
                                    while (reader.Read())
                                        cod_gen = reader.GetString(0);
                                }
                                catch (Exception e)
                                {
                                }
                            }
                            con.Close();

                            query = "UPDATE FILME SET COD_GEN = " + int.Parse(cod_gen) + ", TRAILER = '" + trailer + "', DATA_ESTREIA = " + dataestreia + ", DESCRICAO = '" + descricao + "', DURACAO = '" + duracao + "', URL_IMAGEM = '" + imagem + "' WHERE NOME_FILME = '" + nome + "' AND YEAR(DATA_ESTREIA) = " + int.Parse(ano);
                            command = new OdbcCommand(query, con);
                            con.Open();
                            try
                            {
                                command.ExecuteNonQuery();
                            }
                            catch (Exception e)
                            {
                                Console.WriteLine("ERRO AO INSERIR DADOS FILME: " + e);
                            }
                            con.Close();
                        }
                        else
                        {
                            query = "SELECT COD_GEN FROM GENERO WHERE NOME_GEN = '" + genero + "'";
                            OdbcCommand command = new OdbcCommand(query, con);
                            con.Open();
                            OdbcDataReader reader = command.ExecuteReader();
                            if (reader.HasRows)
                            {
                                try
                                {
                                    while (reader.Read())
                                        cod_gen = reader.GetString(0);
                                }
                                catch (Exception e)
                                {
                                }
                            }
                            else
                            {
                                con.Close();
                                con.Open();
                                query = "INSERT INTO GENERO VALUES ('" + genero + "')";
                                command = new OdbcCommand(query, con);
                                try
                                {
                                    command.ExecuteNonQuery();
                                }
                                catch (Exception e)
                                {
                                    Console.WriteLine("ERRO AO INSERIR DADOS FILME: " + e);
                                }
                                con.Close();
                                query = "SELECT COD_GEN FROM GENERO WHERE NOME_GEN = '" + genero + "'";
                                command = new OdbcCommand(query, con);
                                con.Open();
                                reader = command.ExecuteReader();
                                try
                                {
                                    while (reader.Read())
                                        cod_gen = reader.GetString(0);
                                }
                                catch (Exception e)
                                {
                                }
                            }
                            con.Close();

                            query = "UPDATE FILME SET COD_GEN = " + int.Parse(cod_gen) + ", DATA_ESTREIA = " + dataestreia + ", DESCRICAO = '" + descricao + "', DURACAO = '" + duracao + "', URL_IMAGEM = '" + imagem + "' WHERE NOME_FILME = '" + nome + "' AND YEAR(DATA_ESTREIA) = " + int.Parse(ano);
                            command = new OdbcCommand(query, con);
                            con.Open();
                            try
                            {
                                command.ExecuteNonQuery();
                            }
                            catch (Exception e)
                            {
                                Console.WriteLine("ERRO AO INSERIR DADOS FILME: " + e);
                            }
                            con.Close();
                        }
                    }
                    else
                    {
                        if (trailer.Length > 0)
                        {
                            query = "UPDATE FILME SET TRAILER = '" + trailer + "', DATA_ESTREIA = " + dataestreia + ", DESCRICAO = '" + descricao + "', DURACAO = '" + duracao + "', URL_IMAGEM = '" + imagem + "' WHERE NOME_FILME = '" + nome + "' AND YEAR(DATA_ESTREIA) = " + int.Parse(ano);
                            OdbcCommand command = new OdbcCommand(query, con);
                            con.Open();
                            try
                            {
                                command.ExecuteNonQuery();
                            }
                            catch (Exception e)
                            {
                                Console.WriteLine("ERRO AO INSERIR DADOS FILME: " + e);
                            }
                            con.Close();
                        }
                        else
                        {

                            query = "UPDATE FILME SET DATA_ESTREIA = " + dataestreia + ", DESCRICAO = '" + descricao + "', DURACAO = '" + duracao + "', URL_IMAGEM = '" + imagem + "' WHERE NOME_FILME = '" + nome + "' AND YEAR(DATA_ESTREIA) = " + int.Parse(ano);
                            OdbcCommand command = new OdbcCommand(query, con);
                            con.Open();
                            try
                            {
                                command.ExecuteNonQuery();
                            }
                            catch (Exception e)
                            {
                                Console.WriteLine("ERRO AO INSERIR DADOS FILME: " + e);
                            }
                            con.Close();
                        }
                    }
                }
            }
        }

        public void removerFilme(String nome, String ano)
        {
            string query = "DELETE FROM FILME WHERE NOME_FILME = '" + nome + "' AND YEAR(DATA_ESTREIA) = " + int.Parse(ano);
            OdbcCommand command = new OdbcCommand(query, con);
            con.Open();
            int rows = 0;
            try
            {
                rows = command.ExecuteNonQuery();
                Console.WriteLine(rows);
            }
            catch (Exception e)
            {
                Console.WriteLine("ERRO AO REMOVER DADOS FILME: " + e);
            }
            con.Close();
        }

        public Hashtable obterListaFilmes()
        {
            Hashtable lista = new Hashtable();
            ArrayList listaFilmes;
            string query = "SELECT COD_FILME, NOME_FILME, YEAR(DATA_ESTREIA) FROM FILME";
            OdbcCommand command = new OdbcCommand(query, con);
            con.Open();
            OdbcDataReader reader = command.ExecuteReader();

            try
            {
                while (reader.Read())
                {
                    listaFilmes = new ArrayList();
                    listaFilmes.Add(reader.GetString(1));
                    listaFilmes.Add(reader.GetString(2));
                    lista.Add(reader.GetInt32(0), listaFilmes);
                }

            }
            catch (Exception e)
            {
            }
            con.Close();
            return lista;
        }

        public void obterDadosFilme(String nome, String ano, Filmes f)
        {
            string query = "SELECT COD_FILME, NOME_ACTOR, NOME_REALIZ, NOME_GEN, TRAILER, CLASS_TOT, CLASSIFICACOES, VISITAS, DATA_ESTREIA, DESCRICAO, DURACAO, URL_IMAGEM "
                          + "FROM FILME INNER JOIN ACTOR ON FILME.COD_ACTOR = ACTOR.COD_ACTOR INNER JOIN GENERO ON FILME.COD_GEN = GENERO.COD_GEN INNER JOIN REALIZADOR ON FILME.COD_REALIZ = REALIZADOR.COD_REALIZ "
                          + "WHERE NOME_FILME = '" + nome + "' AND YEAR(DATA_ESTREIA) = " + int.Parse(ano);
            OdbcCommand command = new OdbcCommand(query, con);
            con.Open();
            OdbcDataReader reader = command.ExecuteReader();

            try
            {
                while (reader.Read())
                {
                    if (!reader.IsDBNull(0)) f.codigoFilme = (int)reader.GetInt32(0);
                    if (!reader.IsDBNull(1)) f.nomeActor = reader.GetString(1);
                    if (!reader.IsDBNull(2)) f.nomeRealizador = reader.GetString(2);
                    if (!reader.IsDBNull(3)) f.genero = reader.GetString(3);
                    if (!reader.IsDBNull(4)) f.trailer = reader.GetString(4);
                    if (!reader.IsDBNull(5)) f.classificacaoTotal = (int)reader.GetInt32(5);
                    if (!reader.IsDBNull(6)) f.votos = (int)reader.GetInt32(6);
                    if (!reader.IsDBNull(7)) f.visitas = (int)reader.GetInt32(7);
                    if (!reader.IsDBNull(8)) f.dataEstreia = reader.GetDateTime(8);
                    if (!reader.IsDBNull(9)) f.descricao = reader.GetString(9);
                    if (!reader.IsDBNull(10)) f.duracao = reader.GetString(10);
                    if (!reader.IsDBNull(11)) f.imagem = reader.GetString(11);
                }

            }
            catch (Exception e)
            {
            }
            con.Close();
        }


        public String obterGeneroMaisV()
        {
            string query = "SELECT NOME_GEN FROM GENERO";
            ArrayList generos = new ArrayList();
            OdbcCommand command = new OdbcCommand(query, con);
            con.Open();
            OdbcDataReader reader = command.ExecuteReader();
            try
            {
                while (reader.Read())
                {
                    generos.Add(reader.GetString(0));
                }
            }
            catch (Exception e)
            {
            }
            con.Close();

            Hashtable generos_visitas = new Hashtable();
            IEnumerator en = generos.GetEnumerator();
            while (en.MoveNext())
            {
                query = "SELECT SUM(VISITAS) FROM FILME INNER JOIN GENERO ON FILME.COD_GEN = GENERO.COD_GEN WHERE NOME_GEN = '" + en.Current.ToString() + "'";
                command = new OdbcCommand(query, con);
                con.Open();
                reader = command.ExecuteReader();
                try
                {
                    while (reader.Read())
                    {
                        generos_visitas.Add(reader.GetInt32(0), en.Current.ToString());
                    }
                }
                catch (Exception e)
                {
                }
                con.Close();
            }
            ArrayList visitas = new ArrayList(generos_visitas.Keys);

            visitas.Sort();
            return (String)generos_visitas[visitas[visitas.Count - 1]];
        }

        public int calcularNumeroFilmesBD()
        {
            string query = "SELECT COUNT(COD_FILME) FROM FILME";
            int num_filmes = 0;
            OdbcCommand command = new OdbcCommand(query, con);
            con.Open();
            OdbcDataReader reader = command.ExecuteReader();
            try
            {
                while (reader.Read())
                {
                    num_filmes = reader.GetInt32(0);
                }
            }
            catch (Exception e)
            {
            }
            con.Close();

            return num_filmes;
        }

        public Hashtable obterFilmesMaisV(int num)
        {
            Hashtable filmes = new Hashtable();
            string query = "SELECT NOME_FILME, YEAR(DATA_ESTREIA), VISITAS FROM FILME ORDER BY VISITAS DESC";
            OdbcCommand command = new OdbcCommand(query, con);
            con.Open();
            OdbcDataReader reader = command.ExecuteReader();
            try
            {
                while (reader.Read() && num > 0)
                {
                    filmes.Add(reader.GetString(0), reader.GetString(1));
                    num--;
                }
            }
            catch (Exception e)
            {
            }
            con.Close();
            return filmes;
        }

        public String obterRealizadorMaisV()
        {
            string query = "SELECT NOME_REALIZ FROM REALIZADOR";
            ArrayList realizadores = new ArrayList();
            OdbcCommand command = new OdbcCommand(query, con);
            con.Open();
            OdbcDataReader reader = command.ExecuteReader();
            try
            {
                while (reader.Read())
                {
                    realizadores.Add(reader.GetString(0));
                }
            }
            catch (Exception e)
            {
            }
            con.Close();

            Hashtable realizadores_visitas = new Hashtable();
            IEnumerator en = realizadores.GetEnumerator();
            while (en.MoveNext())
            {
                query = "SELECT SUM(VISITAS) FROM FILME INNER JOIN REALIZADOR ON FILME.COD_REALIZ = REALIZADOR.COD_REALIZ WHERE NOME_REALIZ = '" + en.Current.ToString() + "'";
                command = new OdbcCommand(query, con);
                con.Open();
                reader = command.ExecuteReader();
                try
                {
                    while (reader.Read())
                    {
                        realizadores_visitas.Add(reader.GetInt32(0), en.Current.ToString());
                    }
                }
                catch (Exception e)
                {
                }
                con.Close();
            }
            ArrayList visitas = new ArrayList(realizadores_visitas.Keys);
            visitas.Sort();
            return (String)realizadores_visitas[visitas[visitas.Count - 1]];
        }

        public String obterActorMaisV()
        {
            string query = "SELECT NOME_ACTOR FROM ACTOR";
            ArrayList actores = new ArrayList();
            OdbcCommand command = new OdbcCommand(query, con);
            con.Open();
            OdbcDataReader reader = command.ExecuteReader();
            try
            {
                while (reader.Read())
                {
                    actores.Add(reader.GetString(0));
                }
            }
            catch (Exception e)
            {
            }
            con.Close();

            Hashtable actores_visitas = new Hashtable();
            IEnumerator en = actores.GetEnumerator();
            while (en.MoveNext())
            {
                query = "SELECT SUM(VISITAS) FROM FILME INNER JOIN ACTOR ON FILME.COD_ACTOR = ACTOR.COD_ACTOR WHERE NOME_ACTOR = '" + en.Current.ToString() + "'";
                command = new OdbcCommand(query, con);
                con.Open();
                reader = command.ExecuteReader();
                try
                {
                    while (reader.Read())
                    {
                        actores_visitas.Add(reader.GetInt32(0), en.Current.ToString());
                    }
                }
                catch (Exception e)
                {
                }
                con.Close();
            }
            ArrayList visitas = new ArrayList(actores_visitas.Keys);
            visitas.Sort();
            return (String)actores_visitas[visitas[visitas.Count - 1]];
        }

        public Hashtable obterFilmesMelhorC(int num)
        {
            Hashtable filmes = new Hashtable();
            string query = "SELECT NOME_FILME, YEAR(DATA_ESTREIA), (CAST(CLASS_TOT AS DECIMAL(8,2))/CLASSIFICACOES) AS CLASS FROM FILME ORDER BY CLASS DESC";
            OdbcCommand command = new OdbcCommand(query, con);
            con.Open();
            OdbcDataReader reader = command.ExecuteReader();
            try
            {
                while (reader.Read() && num > 0)
                {
                    filmes.Add(reader.GetString(0), reader.GetString(1));
                    num--;
                }
            }
            catch (Exception e)
            {
            }
            con.Close();
            return filmes;
        }

        public bool existeFilme(String nome, String ano)
        {
            string query = "SELECT COD_FILME FROM FILME WHERE NOME_FILME = '" + nome + "' AND YEAR(DATA_ESTREIA) =" + ano;
            OdbcCommand command = new OdbcCommand(query, con);
            con.Open();
            OdbcDataReader reader = command.ExecuteReader();
            bool existe = reader.HasRows;
            con.Close();
            return existe;
        }

        public int obterVisitasFilme(String nome, String ano)
        {
            int visitas = 0;
            string query = "SELECT VISITAS FROM FILME WHERE NOME_FILME = '" + nome + "' AND YEAR(DATA_ESTREIA) =" + ano;
            OdbcCommand command = new OdbcCommand(query, con);
            con.Open();
            OdbcDataReader reader = command.ExecuteReader();
            try
            {
                while (reader.Read())
                {
                    visitas = reader.GetInt32(0);
                }
            }
            catch (Exception e)
            {
            }
            con.Close();

            return visitas;
        }

        public Hashtable pesquisarPalavra(String chave)
        {
            Hashtable lista = new Hashtable();
            ArrayList listaFilmes;
            string query = "SELECT COD_FILME, NOME_FILME, YEAR(DATA_ESTREIA) FROM FILME WHERE NOME_FILME LIKE '%" + chave + "%'";
            OdbcCommand command = new OdbcCommand(query, con);
            con.Open();
            OdbcDataReader reader = command.ExecuteReader();

            try
            {
                while (reader.Read())
                {
                    listaFilmes = new ArrayList();
                    listaFilmes.Add(reader.GetString(1));
                    listaFilmes.Add(reader.GetString(2));
                    lista.Add(reader.GetInt32(0), listaFilmes);
                }

            }
            catch (Exception e)
            {
            }
            con.Close();
            return lista;
        }

        public int obterClassificacao(String nome, String ano)
        {
            string query = "SELECT CLASS_TOT FROM FILME WHERE NOME_FILME = '" + nome + "' AND YEAR(DATA_ESTREIA) = " + ano;
            int classificacao = 0;
            OdbcCommand command = new OdbcCommand(query, con);
            con.Open();
            OdbcDataReader reader = command.ExecuteReader();

            try
            {
                while (reader.Read())
                {
                    classificacao = reader.GetInt32(0);
                }

            }
            catch (Exception e)
            {
            }
            con.Close();
            return classificacao;
        }

        public int obterNumVotos(String nome, String ano)
        {
            string query = "SELECT CLASSIFICACOES FROM FILME WHERE NOME_FILME = '" + nome + "' AND YEAR(DATA_ESTREIA) = " + ano;
            int votos = 0;
            OdbcCommand command = new OdbcCommand(query, con);
            con.Open();
            OdbcDataReader reader = command.ExecuteReader();

            try
            {
                while (reader.Read())
                {
                    votos = reader.GetInt32(0);
                }

            }
            catch (Exception e)
            {
            }
            con.Close();
            return votos - 1;
        }

        public void novoVoto(String nome, String ano, int voto)
        {
            string query = "UPDATE FILME SET CLASS_TOT = (CLASS_TOT + " + voto + "), CLASSIFICACOES = (CLASSIFICACOES+1) WHERE NOME_FILME = '" + nome + "' AND YEAR(DATA_ESTREIA) =" + ano;
            OdbcCommand command = new OdbcCommand(query, con);
            con.Open();
            try
            {
                command.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Console.WriteLine("ERRO AO INSERIR NOVO VOTO: " + e);
            }
            con.Close();

        }

        public void alterarVoto(String nome, String ano, int voto, int voto_antigo)
        {
            int diferenca = voto - voto_antigo;
            string query = "UPDATE FILME SET CLASS_TOT = (CLASS_TOT + " + diferenca + ") WHERE NOME_FILME = '" + nome + "' AND YEAR(DATA_ESTREIA) =" + ano;
            OdbcCommand command = new OdbcCommand(query, con);
            con.Open();
            try
            {
                command.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Console.WriteLine("ERRO AO ALTERAR VOTO: " + e);
            }
            con.Close();
        }

        public ArrayList obterLocalidades(String nome, String ano)
        {
            ArrayList localidades = new ArrayList();
            string query = "SELECT DISTINCT LOCALIDADE FROM FILME INNER JOIN HORARIO_CINEMA_SALA_FILME ON FILME.COD_FILME = HORARIO_CINEMA_SALA_FILME.COD_FILME INNER JOIN CINEMA ON HORARIO_CINEMA_SALA_FILME.COD_CINEMA = CINEMA.COD_CINEMA INNER JOIN LOCALIDADE ON CINEMA.COD_LOCAL = LOCALIDADE.COD_LOCAL WHERE NOME_FILME = '" + nome + "' AND YEAR(DATA_ESTREIA) =" + ano;
            OdbcCommand command = new OdbcCommand(query, con);
            con.Open();
            OdbcDataReader reader = command.ExecuteReader();
            try
            {
                while (reader.Read())
                {
                    localidades.Add(reader.GetString(0));
                }

            }
            catch (Exception e)
            {
            }
            con.Close();

            return localidades;
        }
    }
}